﻿using Microsoft.Office.Interop.Excel;
using MicrosoftExcelApplication = Microsoft.Office.Interop.Excel.Application; 
using ExcelRange = Microsoft.Office.Interop.Excel.Range; 
using SUNRPA.CONTROLS.IMPL.domain.constant;
using System.Runtime.InteropServices.ComTypes;
using System.Runtime.InteropServices;
using SUNRPA.CONTROLS.IMPL.domain;
using System.Text;
using System.Reflection;

namespace SUNRPA.CONTROLS.IMPL.util {
    //每一个excelutil对象表示了对于一个excel应用窗口的操作
    //再ms中excel所有单元格都被称为range。一个区域，一行、一列、一个单元格都是一个区域，所有操作是针对区域操作。
    //range有两种用法一种是用range属性一种使用get_Range方法，属性直接写单元格，方法用get_Range("A1:A2", missing.vale)标识
    //区域还可以声明变量来追踪，例如使用Application.get_Range("varName", missing.value)则代表声明了一个名字为varName的区域
    //之后则可以使用
    public class ExcelUtil {
        private Type excelType;
        public MicrosoftExcelApplication? app;
        public Workbooks? wbs;
        public Workbook? wb;
        public Worksheets? wss;
        public Worksheet? ws;
        public ExcelUtil([Optional]Type type) { 
            excelType = type;
            app = GetExistExcelObj() as MicrosoftExcelApplication;
        }
        public Workbook Create(bool isVisable, string excelPath) {
            if(app == null) { 
                app = Activator.CreateInstance(excelType) as MicrosoftExcelApplication;
            }
            app.DisplayAlerts = false;
            app.DefaultFilePath = excelPath;
            wbs = app.Workbooks;
            wb = wbs.Add(true);
            app.Visible = isVisable;
            app.DisplayAlerts = true;
            return wb;
        }


        public Workbook Open(bool isVisable, string path, string password) {
            if(app == null) { 
                app = Activator.CreateInstance(excelType) as MicrosoftExcelApplication;
            }
            app.DisplayAlerts = false;
            app.Visible = isVisable;
            wb = app.Workbooks.Open(path, 
                Type.Missing, Type.Missing, Type.Missing, password, 
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
                Type.Missing, Type.Missing);
            app.DisplayAlerts = true;
            return wb;
        }

        public Worksheet? GetWorkSheet(string sheetName, bool nullActive) { 
            Worksheet sheet;
            if(nullActive && String.IsNullOrEmpty(sheetName)) {
                sheet = wb.ActiveSheet as Worksheet;
            } else { 
                sheet = wb.Worksheets[sheetName] as Worksheet;
            }
            if(sheet == null) { 
                throw new RpaRuntimeException("sheet不存在");
            }
            return sheet;
        }
        
        private object? GetExistExcelObj() { 
            IRunningObjectTable rot = SystemUtil.GetROT4Com();
            SystemUtil.GetSpecificComObjByGuid(DefaultContents.EXCEL_APPLICATION_ID.ToString(), rot, out object comObj);
            return comObj;
        }
        public void InsertColumn(string sheetName, int columnNum){
            app.DisplayAlerts = false;
            Worksheet sheet = this.GetWorkSheet(sheetName, true);
            ExcelRange column = sheet.Columns[columnNum, Missing.Value] as ExcelRange;
            column.Insert(XlInsertShiftDirection.xlShiftToRight, Missing.Value);
            app.DisplayAlerts = true;
        }
        public void InsertRow(string sheetName, int rowNum) {
            app.DisplayAlerts = false;
            Worksheet sheet = this.GetWorkSheet(sheetName, true);
            ExcelRange row = sheet.Rows[rowNum, Missing.Value] as ExcelRange;
            row.Insert(XlInsertShiftDirection.xlShiftDown, Missing.Value);
            app.DisplayAlerts = true;
        }

        public void FillColumn2End(int rowNum, int columnNum, string sheetName, object fillContent, int writeWay = 0) {
            app.DisplayAlerts = false;
            Worksheet sheet = this.GetWorkSheet(sheetName, true);
            try { 
                app.ScreenUpdating = false;
                int totalRows = GetLastUsableRow(sheetName);
                int totalCols = GetLastUsableColumn(sheetName);
                if(writeWay == 0) { 
                    columnNum = totalCols + 1;
                }
                if(writeWay == 1) {
                    //插入
                    this.InsertColumn(sheetName, columnNum);
                } 
                if(fillContent is string) {
                    string content = fillContent as string;
                    ExcelRange targetRange = this.GetRange(sheetName, rowNum, columnNum, totalRows, columnNum);
                    sheet.Cells[rowNum, columnNum] = content;
                    ((ExcelRange)sheet.Cells[rowNum, columnNum]).AutoFill(targetRange, XlAutoFillType.xlFillCopy);
                    /*for(int i = rowNum; i <= totalRows; i++) {
                        //覆盖+追加
                        sheet.Cells[i, columnNum] = content;
                    }*/
                } else if(fillContent is List<string>){
                    List<string> content = fillContent as List<string>;
                    for(int i = rowNum; i < content.Count + rowNum; i++) {
                        //覆盖+追加
                        sheet.Cells[i, columnNum] = content[i - rowNum];
                    }
                } else { 
                    throw new RpaRuntimeException("需要填充的参数类型不正确,需要单个字符串或者列表");
                }
            }finally { 
                app.ScreenUpdating = true;
                sheet.Calculate();
                app.DisplayAlerts = true;
            }
        }

        public void FillRow2End(int rowNum, int columnNum, string sheetName, object fillContent, int writeWay = 0) {
            app.DisplayAlerts = false;
            Worksheet sheet = this.GetWorkSheet(sheetName, true);
            try { 
                app.ScreenUpdating = false;
                int totalRows = GetLastUsableRow(sheetName);
                int totalCols = GetLastUsableColumn(sheetName);
                if(writeWay == 0) {
                    rowNum = totalRows + 1;
                }
                if(writeWay == 1) {
                    //插入
                    this.InsertRow(sheetName, rowNum);
                }
                if(fillContent is string) {
                    string content = fillContent as string;
                    ExcelRange targetRange = this.GetRange(sheetName, rowNum, columnNum, rowNum, totalCols);
                    sheet.Cells[rowNum, columnNum] = content;
                    ((ExcelRange)sheet.Cells[rowNum, columnNum]).AutoFill(targetRange, XlAutoFillType.xlFillCopy);
                    /*for(int i = columnNum; i <= totalCols; i++) {
                        sheet.Cells[rowNum, i] = content;
                    }*/
                } else if(fillContent is List<string>) {
                    List<string> content = fillContent as List<string>;
                    for(int i = columnNum; i < content.Count + columnNum; i++) {
                        //覆盖+追加
                        sheet.Cells[rowNum, i] = content[i - rowNum];
                    }
                } else {
                    throw new RpaRuntimeException("需要填充的参数类型不正确,需要单个字符串或者列表");
                }
            }finally { 
                app.ScreenUpdating = true;
                sheet.Calculate();
                app.DisplayAlerts = true;
            }
            
        }

        public void FillCell(int rowNum, int columnNum, string sheetName, object fillContent) { 
            app.DisplayAlerts = false;
            try {
                Worksheet sheet = this.GetWorkSheet(sheetName, true);
                if(fillContent is string) {
                    string content = fillContent as string;
                    sheet.Cells[rowNum, columnNum] = content;
                } else { 
                    throw new RpaRuntimeException("需要填充的参数类型不正确,需要单个字符串");
                }
            }finally { 
                app.DisplayAlerts = true;
            }
        }

        public int GetRowNum(string rowString, string sheetName = "") {
            int totalRows = GetLastUsableRow(sheetName);
            bool flag = int.TryParse(rowString, out int rowNum);
            if(!flag || rowNum == 0) { 
                return 0;
            }
            if(rowNum > 0) {
                return rowNum;
            } else { 
                return totalRows + rowNum + 1;
            }
        }

        public int GetColumnNum(string columnString, string sheetName = "") { 
            int totalColumn = GetLastUsableColumn(sheetName);
            if(int.TryParse(columnString, out int columnNum)) { 
                if(columnNum >= 0) {
                    return columnNum;
                } else { 
                    return totalColumn + columnNum + 1;
                }
            } else { 
                return GetColumnIndex(columnString);
            }
        }


         /// <summary>
        /// 获取Excel实际列索引
        /// </summary>
        /// <param name="columnName">Excel列名</param>
        /// <returns>int格式索引</returns>
        public static int GetColumnIndex(string columnName){
            int result = 0;
            // A-Z 转换成 0-25的数字，并反转
            int[] colReverse = Encoding.ASCII.GetBytes(columnName.ToUpper()).Select(x =>{
                int aIndex = Encoding.ASCII.GetBytes("A")[0];
                int zIndex = Encoding.ASCII.GetBytes("Z")[0];
                return x < aIndex || x > zIndex ? 0 : x - aIndex;
            }).Reverse().ToArray();
            // 当成26进制数，遍历计算
            for (int i = 0; i < colReverse.Count(); i++){
                // 个位(0~25)  其他位(1~26)
                int vReal = colReverse[i] + (i == 0 ? 0 : 1);
                result += vReal * (int)Math.Pow(26, i);
            }
            return result + 1;
        }

        public void SetApp(Workbook wb) { 
            this.wb = wb;
            app = wb.Application;
        }

        public void CloseCurrentExcel() { 
            this.wb.Close();
        }

        public void SaveWorkBook() { 
            this.wb.Save();
        }

        /// <summary>
        /// workbook另存为
        /// </summary>
        /// <param name="path">路径</param>
        /// <param name="cover">是否覆盖</param>
        /// <exception cref="RpaRuntimeException"></exception>
        public void WorkBookSaveAs(string path, bool cover) {
            app.DisplayAlerts = false;
            try { 
                if(File.Exists(path)) {
                    if(cover) { 
                        File.Delete(path);
                    } else { 
                        throw new RpaRuntimeException("另存为目标文件已存在");
                    }
                }
                this.wb.SaveAs(path);
            }finally { 
                app.DisplayAlerts = true;
            }
        }
        /// <summary>
        /// 关闭所有excel
        /// </summary>
        public static void CloseAllExcel() { 
            IRunningObjectTable rot = SystemUtil.GetROT4Com();
            rot.EnumRunning(out IEnumMoniker enumMoniker);
            var moniker = new IMoniker[1];
            while (enumMoniker.Next(1, moniker, IntPtr.Zero) == 0){
                User32Utils.CreateBindCtx(0, out IBindCtx bindCtx);
                moniker[0].GetDisplayName(bindCtx, null, out string displayName);
                rot.GetObject(moniker[0], out object comObj);
                string id = SystemUtil.ConversionGuid(displayName);
                if(DefaultContents.EXCEL_APPLICATION_ID.ToString().ToLower().Equals(id.ToLower())) { 
                    MicrosoftExcelApplication obj = comObj as MicrosoftExcelApplication;
                    if(obj != null) { 
                        obj.Quit();
                        Marshal.ReleaseComObject(obj);
                    }
                }
            }
        }

        public static Type? GetAutoExcelDriver(ref int driver) { 
            driver = 2;
            Type type;
            type = Type.GetTypeFromProgID("ET.Application");//V8版本类型
            if (type == null){
                type = Type.GetTypeFromProgID("Ket.Application");//V9版本类型
                if (type == null){
                    type = Type.GetTypeFromProgID("Kwps.Application");//V10版本类型
                    if (type == null){
                        type = Type.GetTypeFromProgID("EXCEL.Application");//MS EXCEL类型
                        driver = 1;
                    }
                }
            }
            return type;
        }
        public ExcelRange? GetRange(string sheetName, [Optional]int row, [Optional]int column, [Optional]int endRow, [Optional]int endColumn) {
            Worksheet sheet = this.GetWorkSheet(sheetName, true);
            if(row != 0 && column == 0 && endRow == 0 && endColumn == 0) {
                //行
                return sheet.UsedRange.Rows[row, Missing.Value] as ExcelRange;
            }else if(row == 0 && column != 0 && endRow == 0 && endColumn == 0) { 
                //列
                return sheet.UsedRange.Columns[column, Missing.Value] as ExcelRange;
            }else if(row != 0 && column != 0 && endRow == 0 && endColumn == 0) { 
                //单元格
                return sheet.Cells[row, column] as ExcelRange;
            }else if(row != 0 && column != 0 && endRow != 0 && endColumn != 0) { 
                //区域
                return sheet.Range[sheet.Cells[row, column], sheet.Cells[endRow, endColumn]] as ExcelRange;
            } else { 
                return sheet.UsedRange;
            }
        }

        public void CopyRang(string sheetName, int rangeType,[Optional]int row, [Optional]int column, [Optional]int endRow, [Optional]int endColumn) { 
            ExcelRange copyRange;
            switch (rangeType) { 
                case 0: { 
                    //区域
                    copyRange = GetRange(sheetName, row, column, endRow, endColumn);
                    break;    
                } 
                case 1: { 
                    //列
                    copyRange = GetRange(sheetName, column:column);
                    break;    
                } 
                case 2: {
                    //行
                    copyRange = GetRange(sheetName, row:row);
                    break;    
                } 
                case 3: { 
                    //单元格
                    copyRange = GetRange(sheetName, row, column);
                    break;    
                } 
                case 4: { 
                    //已使用区域
                    copyRange = GetRange(sheetName);
                    break;    
                } 
                default: throw new RpaRuntimeException("复制区域类型错误");
            }
            if(copyRange != null) {
                //复制到系统剪切板
                copyRange.Copy();
            }
        }

        public void CopyPivotTable(string pivotNameOrIndex, string sheetName) { 
            Worksheet ws = GetWorkSheet(sheetName, true);
            PivotTable pt;
            if(int.TryParse(pivotNameOrIndex, out int index)) {
                pt = ws.PivotTables(index) as PivotTable;
            } else { 
                pt = ws.PivotTables(pivotNameOrIndex) as PivotTable;
            }
            if(pt == null) { 
                throw new RpaRuntimeException($"没有找到数据透视表{pivotNameOrIndex}");
            }
            ExcelRange range = pt.TableRange2;
            range.Copy();
        }
        /// <summary>
        /// 粘贴透视表
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="startRow"></param>
        /// <param name="startColumn"></param>
        /// <param name="copyType">0保留原格式、1只粘贴值</param>
        public void PastePivotTable(string sheetName, int startRow, int startColumn, int copyType) { 
            Worksheet ws = GetWorkSheet(sheetName, true);
            ExcelRange range = GetRange(sheetName, startRow, startColumn);
            switch(copyType) {
                case 0:
                    ws.Paste(range);
                    break;
                case 1:
                    range.PasteSpecial(XlPasteType.xlPasteValues);
                    break;
                default:
                    break;
            }
        }
        /// <summary>
        /// 数字(Range.NumberFormatlocal 属性)
        ///常规：Range.NumberFormatlocal = "G/通用格式"
        ///数值：Range.NumberFormatlocal = "0.000_ " --保留小数位数为3
　　　  ///Range.NumberFormatlocal = "0" --不要小数
        ///Range.NumberFormatlocal = "#,##0.000 " --保留小数位数为3，并使用千位分隔符
        ///货币：Range.NumberFormatlocal = "$#,##0.000"
        ///百分比：Range.NumberFormatlocal = "0.000%"
        ///分数：Range.NumberFormatlocal = "# ?/?"
        ///科学计数：Range.NumberFormatlocal = "0.00E+00"
        ///文本：Range.NumberFormatlocal = "@"
        ///特殊：Range.NumberFormatlocal = "000000"---邮政编码
        ///Range.NumberFormatlocal = "[DBNum1]G/通用格式"---中文小写数字
        ///Range.NumberFormatlocal = "[DBNum2]G/通用格式"---中文大写数字
        ///Range.NumberFormatlocal = "[DBNum2][$RMB]G/通用格式"---人民币大写
        ///对齐
        ///水平对齐：Range.HorizontalAlignment = etHAlignCenter ---居中
        ///垂直对齐：Range.VerticalAlignment = etVAlignCenter---居中
        ///是否自动换行：Range.WrapText = True
        ///是否缩小字体填充：Range.ShrinkToFit = True
        ///是否合并单元格：Range.MergeCells = False
        ///文字竖排：Range.Orientation = etVertical
        ///文字倾斜度数：Range.Orientation = 45 -----倾斜45度
        ///字体（Font对象）
        ///字体名称：Font.Name = "华文行楷"
        ///字形：Font.FontStyle = "常规"
        ///字号：Font.Size = "10"
        ///下划线：Font.Strikethrough = True; Font.Underline = etUnderlineStyleDouble ---双下划线
        ///上标：Font.Superscript = True
        ///下标：Font.SubScript = True
        ///删除线：Font.OutlineFont = True
        ///边框（Borders对象）
        ///Borders.Item(etEdgeTop)：上边框
        ///Borders.Item(etEdgeLeft)：左边框
        ///Borders.Item(etEdgeRight)：右边框
        ///Borders.Item(etEdgeBottom)：下边框
        ///Borders.Item(etDiagonalDown)：左上--右下边框
        ///Borders.Item(etDiagonalUp)：左下--右上边框
        ///Border.LineStyle = etContinuous 线条样式
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="rangeType"></param>
        /// <param name="targetType"></param>
        /// <param name="row"></param>
        /// <param name="column"></param>
        /// <param name="endRow"></param>
        /// <param name="endColumn"></param>
        /// <param name="precision"></param>
        /// <exception cref="RpaRuntimeException"></exception>
        public void SwitchCellDataType(string sheetName, int rangeType, int targetType, [Optional]int row, [Optional]int column, [Optional]int endRow, [Optional]int endColumn, [Optional]int precision) {
            try {
                app.DisplayAlerts = false;
                app.ScreenUpdating = false;
                ExcelRange range;
                switch(rangeType) {
                    case 3: {
                            //区域
                            range = GetRange(sheetName, row, column, endRow, endColumn);
                            break;
                        }
                    case 0: {
                            //列
                            range = GetRange(sheetName, column: column);
                            break;
                        }
                    case 1: {
                            //行
                            range = GetRange(sheetName, row: row);
                            break;
                        }
                    case 2: {
                            //单元格
                            range = GetRange(sheetName, row, column);
                            break;
                        }
                    case 4: {
                            //已使用区域
                            range = GetRange(sheetName);
                            break;
                        }
                    default:
                        throw new RpaRuntimeException("区域类型错误");
                }
                int rows = range.Rows.Count > GetLastUsableRow(sheetName) ? GetLastUsableRow(sheetName) : range.Rows.Count;
                int columns = range.Columns.Count > GetLastUsableColumn(sheetName) ? GetLastUsableColumn(sheetName) : range.Columns.Count;
                range = range.Resize[rows, columns];
                /*object[,] cellData = new object[rows, columns];
                object[,] oriData = (object[,])range.Value;
                for(int i = 0; i < rows; i++) {
                    for(int j = 0; j < columns; j++) { 
                        object oriValue = oriData[i+1, j+1];
                        if(oriValue == null) { 
                            continue;
                        }
                        switch (targetType) { 
                            case 0:
                                if(int.TryParse(oriValue.ToString(), out int target)) { 
                                    cellData[i, j] = target;
                                } else { 
                                    cellData[i, j] = oriValue;
                                }
                                break;
                            case 1:
                                if(oriValue != null) { 
                                    cellData[i, j] = oriValue.ToString();
                                }
                                break;
                            case 2:
                                //小数
                                break;
                            case 3:
                                //百分比
                                break;
                            default:
                                break;
                        }
                    }
                }
                range.set_Value(Missing.Value, cellData);*/
                switch (targetType) { 
                    case 0:
                        range.NumberFormatLocal = "0";
                        break;
                    case 1:
                        range.NumberFormatLocal = "@";
                        break;
                    case 2:
                        StringBuilder format = new StringBuilder("0");
                        for(int i=0; i<precision; i++) {
                            if(i == 0) {
                                format.Append(".0");
                            } else { 
                                format.Append("0");
                            }
                        }
                        format.Append("_ ");
                        range.NumberFormatLocal = format.ToString();
                        //小数
                        break;
                    case 3:
                        format = new StringBuilder("0");
                        for(int i=0; i<precision; i++) {
                            if(i == 0) {
                                format.Append(".0");
                            } else { 
                                format.Append("0");
                            }
                        }
                        format.Append("%");
                        range.NumberFormatLocal = format.ToString();
                        //百分比
                        break;
                    default:
                        break;
                }
            }finally { 
                app.ScreenUpdating = true;
                app.DisplayAlerts = true;
            }
        }
        public void PaestRange(string sheetName, int row, int column) { 
            app.DisplayAlerts = false;
            Worksheet sheet = this.GetWorkSheet(sheetName, true);
            sheet.Paste(sheet.Cells[row, column]);
            app.DisplayAlerts = true;
        }

        public int GetFirstUseableRow(string sheetName) { 
            Worksheet sheet = this.GetWorkSheet(sheetName, true);
            return (sheet.UsedRange.Rows[1, Missing.Value] as ExcelRange).Row;
        }

        public int GetFirstUsableColumn(string sheetName) { 
            Worksheet sheet = this.GetWorkSheet(sheetName, true);
            return (sheet.UsedRange.Columns[1, Missing.Value] as ExcelRange).Column;        
        }

        public int GetLastUsableRow(string sheetName) { 
            Worksheet sheet = this.GetWorkSheet(sheetName, true);
            return (sheet.UsedRange.Rows[sheet.UsedRange.Rows.Count, Missing.Value] as ExcelRange).Row;
        }

        public int GetLastUsableColumn(string sheetName) { 
            Worksheet sheet = this.GetWorkSheet(sheetName, true);
            return (sheet.UsedRange.Columns[sheet.UsedRange.Columns.Count, Missing.Value] as ExcelRange).Column;
        }

        public int GetFisrtUsableColumnInRow(string sheetName, int rowNum) { 
            Worksheet sheet = this.GetWorkSheet(sheetName, true);
            ExcelRange row = sheet.Rows[rowNum, Missing.Value] as ExcelRange;
            ExcelRange firstCell = row.Cells[Missing.Value, 1] as ExcelRange;
            if(!string.IsNullOrEmpty(firstCell.Value.ToString())) { 
                return 1;
            } 
            return row.Cells.Find(What:"*",SearchOrder:XlSearchOrder.xlByColumns,SearchDirection:XlSearchDirection.xlNext).Column;
        }
        public int GetFisrtUsableRowInColumn(string sheetName, int columnNum) {
            Worksheet sheet = this.GetWorkSheet(sheetName, true);
            ExcelRange column = sheet.Columns[columnNum, Missing.Value] as ExcelRange;
            ExcelRange firstCell = column.Cells[1, Missing.Value] as ExcelRange;
            if(!string.IsNullOrEmpty(firstCell.Value.ToString())) { 
                return 1;
            } 
            return column.Cells.Find(What:"*",SearchOrder:XlSearchOrder.xlByRows,SearchDirection:XlSearchDirection.xlNext).Row;
        }

        public void RenameSheetName(string oriSheetName, string newSheetName) { 
            Worksheet sheet = this.GetWorkSheet(oriSheetName, true);
            sheet.Name = newSheetName;
        }

        /// <summary>
        /// 隐藏或者显示sheet
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="hideOrShow"></param>
        public void HideOrShowSheet(string sheetName, int hideOrShow) { 
            Worksheet sheet = this.GetWorkSheet(sheetName, true);
            if(hideOrShow == 0) {
                sheet.Visible = XlSheetVisibility.xlSheetHidden;
            } else { 
                sheet.Visible = XlSheetVisibility.xlSheetVisible;
            }
        }

        public void AddSheet(int pos, string sheetName) {
            if(pos == 0) {
                Worksheet newSheet = this.wb.Worksheets.Add() as Worksheet;
                if(!string.IsNullOrEmpty(sheetName)) {
                    newSheet.Name = sheetName;
                } 
            } else { 
                Worksheet newSheet = this.wb.Worksheets.Add(After:wb.Worksheets[wb.Worksheets.Count]) as Worksheet;
                if(!string.IsNullOrEmpty(sheetName)) {
                     newSheet.Name = sheetName;
                }
            }
        }

        public void SetFilter(string sheetName, string column, string value) { 
            Worksheet sheet = this.GetWorkSheet(sheetName, true);
            int colNum = this.GetColumnNum(column, sheetName);
            sheet.UsedRange.AutoFilter(colNum,value, VisibleDropDown:true);
        }

        public void RemoveFilter(string sheetName) { 
            Worksheet sheet = this.GetWorkSheet(sheetName, true);
            sheet.UsedRange.AutoFilter(VisibleDropDown:false);
        }

        public int[] GetCellPosition(String searchValue, ExcelRange searchRange) { 
            object[,] values = (object[,])searchRange.Value;
            for(int i = 0; i < values.GetLength(0); i++) {
                for(int j = 0; j < values.GetLength(1); j ++) { 
                    object value = values[i + 1,j + 1];
                    if(value == null) { 
                        continue;
                    }
                    string valueString = value.ToString();
                    if(double.TryParse(valueString, out double valueD) 
                        && double.TryParse(searchValue, out double searchValueD)) {
                        if(valueD.Equals(searchValueD)) { 
                            return new int[] {i + searchRange.Row,j + searchRange.Column};
                        }
                    } else {
                        if(valueString.Equals(searchValue)) { 
                            return new int[] {i + searchRange.Row,j + searchRange.Column};
                        }
                    }
                }
            }
            throw new RpaRuntimeException($"没有找到值为{searchValue}的单元格");
        }
        /// <summary>
        /// workbook管理者所有cache
        /// </summary>
        /// <param name="sourceSheet"></param>
        /// <param name="targetSheet"></param>
        /// <param name="targetRow"></param>
        /// <param name="targetColumn"></param>
        /// <param name="name"></param>
        /// <param name="fields"></param>
        /// <returns></returns>
        public PivotTable CreatePiovtTable(string sourceSheet, string targetSheet, string targetRow, 
            string targetColumn, string name, string rowFields, string columnFields, List<string> valAndTypes) {
            Worksheet ss = this.GetWorkSheet(sourceSheet, true);
            Worksheet ts = this.GetWorkSheet(targetSheet, true);
            int tr = this.GetRowNum(targetRow, targetSheet);
            int tc = this.GetColumnNum(targetColumn, targetSheet);
            ExcelRange dataSource = ss.UsedRange;

            //创建缓存
            PivotCache pc = this.wb.PivotCaches().Create(XlPivotTableSourceType.xlDatabase, dataSource);
            PivotTables pts = ts.PivotTables() as PivotTables;

            PivotTable pt = pts.Add(pc, ts.Cells[tr, tc], name);
            pt.ShowTableStyleRowStripes = true;

            foreach(string rowField in rowFields.Split(',')) { 
                if(string.IsNullOrEmpty(rowField)) { 
                    continue;
                }
                PivotField pf = pt.PivotFields(rowField) as PivotField;
                if(pf == null) {
                    throw new RpaRuntimeException("没有找到数据透视属性");
                }
                pf.Orientation = XlPivotFieldOrientation.xlRowField;
            }

            foreach(string columnField in columnFields.Split(',')) {
                if(string.IsNullOrEmpty(columnField)) { 
                    continue;
                }
                PivotField pf = pt.PivotFields(columnField) as PivotField;
                if(pf == null) {
                    throw new RpaRuntimeException("没有找到数据透视属性");
                }
                pf.Orientation = XlPivotFieldOrientation.xlColumnField;
            }

            foreach(string valueAndCalType in valAndTypes){ 
                string[] vat = valueAndCalType.Split(new string[] {"==="}, StringSplitOptions.RemoveEmptyEntries);
                string valName = vat[0];
                int calType = int.Parse(vat[1]);
                XlConsolidationFunction calFun;
                switch(calType) {
                    case 0:
                        //计数
                        calFun = XlConsolidationFunction.xlCount;
                        break;
                    case 1:
                        //求和
                        calFun = XlConsolidationFunction.xlSum;
                        break;
                    default:
                        calFun = XlConsolidationFunction.xlCountNums;
                        break;
                }
                PivotField pf = pt.PivotFields(valName) as PivotField;
                pt.AddDataField(pf, Function:calFun);
            }
            pt.ColumnGrand = true;
            pt.RowAxisLayout(XlLayoutRowType.xlOutlineRow);
            pt.Update();
            return pt;
        }
    }
}
